package seotools.positions.direct;

import data.DataHelper;
import data.ExtramobiNew;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import mat.xls.*;
import obj.*;

import java.io.*;
import java.util.*;

public class DirectExcelAd {
    //    private static final String ACCUM_BASE_URL = "http://extramobi.ru/product/accumulator_";
//    private static final String CHARGER_NETWORK_BASE_URL = "http://extramobi.ru/product/setevoe_zaryadnoe_ustroystvo_";
//    private static final String CHARGER_AUTO_BASE_URL = "http://extramobi.ru/product/avtomobilnoe_zaryadnoe_ustroystvo_";
    private static final String BODY_BASE_URL = "http://extramobi.ru/product/korpus_";
    private static final String AVITO_AD_URL = "http://www.avito.ru/sankt-peterburg/telefony/originalnye_akkumulyatory_nokia_samsung_se_moto_105344419";
    private static final String MAT_BASE_URL = "http://gloryauto.ru/product/kovrik_v_salon_";
    private static final double AD_MAX_PRICE = 0.22;

    public static int count = 1;

    public static final String ACCUMS_STOP_PHRASES = " -craftmann -maxcell -maybell -аналог -аналоги -быстро -вздулся -где -держит -повышенной -емкости -емкость " +
            "-заряд -как -какая -какой -почему -китайский -китайского -магазин -параметры -плохо -размер -разряжается -садится -сколько " +
            "-совместимость -схема -усиленный -характеристики -бесплатно -видео -фото -фотоаппарат -фотоаппарата";

    public static final String CHARGERS_STOP_PHRASES = " -идет -индикатор -гнездо -коннектор -аналоги -распиновка -через -компьютера " +
            "-напряжение -заряд -как -какая -какой -почему -распайка -полярность -время -параметры -чехол -плохо -велосипедная -поддерживается -сколько " +
            "-совместимость -схема -ремонт -характеристики -бесплатно -видео -фото -фотоаппарат -фотоаппарата";

    public static final String BODIES_STOP_PHRASES = " -металлическом -где -как -оригинал -титановый -железный -замена -оригинальный -оригинальные " +
            "-сменный -металл -стальным -резиновом -золотом -original -бесплатно";

    public static final String REMONT_STOP_PHRASES = " -скачать -бесплатно -видео -программа -версия -как -почему -software -updater -через -phoenix -можно -jaf -android " +
            "-гарантийный -москве -минске -форум -зарядного -своими -руками -гарантии -киев -мануал -официальный -сервис -ноутбуков -телевизоров -стиральных -мониторов " +
            "-пылесосов -принтеров -фотоаппаратов -печи -печей -печь -жк -свч -микроволновки -dvd -мфу -видеокамер -музыкальных -lcd -картриджа -тв -tv -hdd -кинотеатра " +
            "-монитора -видеомагнитофонов -жестких -схемы -кондиционеров -syncmaster";

    public static int adCount = 1;
    public static int phraseCount = 1;

    public static final int SHIPPING_COST_REGION = 220;
    public static final int SHIPPING_COST_SPB = 220;
    public static final int SHIPPING_COST_MOSCOW = 220;

    private static WritableWorkbook workbook;

    private static Map<String, String> commonProps;
    public static final String ALL_RUSSIA_REGIONS = "Центр, -Москва и область, Северо-Запад, -Санкт-Петербург и Ленинградская область, Поволжье, Юг, Сибирь, Дальний Восток, Северный Кавказ, Урал";
    public static final String ALL_RUSSIA_REGIONS_WO_MILLIONS = "Центр, -Москва и область, Северо-Запад, -Санкт-Петербург и Ленинградская область, Поволжье, -Нижний Новгород, -Пермь, -Уфа, -Казань, -Самара, -Саратов, Юг, -Краснодар, -Ростов-на-Дону, Сибирь, -Красноярск, -Новосибирск и область, -Омск и область, Дальний Восток, -Хабаровск, Северный Кавказ, Урал, -Екатеринбург, -Челябинск";
    public static final String SPB_REGION = "Санкт-Петербург и Ленинградская область";
    public static final String MOSCOW_REGION = "Москва и область";

    public static void main(String[] args) throws IOException, WriteException, BiffException {
        commonProps = ExtramobiNew.readCommonProps();

        Ad ad = getAd(AdType.ACCUM);
//        generateAd("data/extramobi/accum/", "direct/accumsNokSEMotoSpb.xls", AdRegion.SPB, 100, 0.26, ad, "Nokia", "Sony Ericsson", "Motorola");
//        generateAd("data/extramobi/accum/", "direct/accumsNokSEMotoRegion.xls", AdRegion.REGION_WITH_MILLION, 100, 0.24, ad, "Nokia", "Sony Ericsson", "Motorola");
//        generateAd("data/extramobi/accum/", "direct/accumsSamHTCAppleSpb.xls", AdRegion.SPB, 100, 0.33, ad, "Samsung", "Apple", "HTC");
//        generateAd("data/extramobi/accum/", "direct/accumsSamHTCAppleRegion.xls", AdRegion.REGION_WITH_MILLION, 100, 0.33, ad, "Samsung", "Apple", "HTC");

        generateAvitoAds("data/extramobi/accum/", "direct/avito_accumsNokiaSEMoto.xls", AdRegion.SPB, 100, 0.2, "Nokia", "Sony Ericsson", "Motorola");
//        generateAd("data/extramobi/accum-photo/", "direct/accumsSonyCanonSpb.xls", AdRegion.SPB, 100, 0.33, ad, "Sony", "Canon");
//        generateAd("data/extramobi/accum/", "direct/accumsNokSEMoscow.xls", AdRegion.MOSCOW, 100, 0.24, ad, "Nokia", "Sony Ericsson");
//        generateAd("data/extramobi/accum-photo/", "direct/accumsSonyCanonMoscow.xls", AdRegion.MOSCOW, 100, 0.30, ad, "Sony", "Canon");
//        generateAd("data/extramobi/accum/", "direct/accumsSamMotAppBlackHTCLGSiemAlcMoscow.xls", AdRegion.MOSCOW, 100, 0.30, ad, "Samsung", "Motorola", "Apple", "Blackberry", "HTC", "LG", "Siemens", "Alcatel");

//        generateAd("data/extramobi/accum-pad/", "direct/accumsIpadTab.xls", AdRegion.SPB, 100, 0.33, ad, "Apple", "Samsung");
//        generateAd("data/extramobi/accum/", "direct/accumsNokSERegoin.xls", AdRegion.REGION_WITH_MILLION, 100, 0.22, ad, "Nokia", "Sony Ericsson");
//        generateAd("data/extramobi/accum-photo/", "direct/accumsSonyCanonRegion.xls", AdRegion.REGION_WITH_MILLION, 100, 0.30, ad, "Sony", "Canon");
//        generateAd("data/extramobi/accum/", "direct/accumsSamMotAppBlackHTCLGSiemAlcRegion.xls", AdRegion.REGION_WITH_MILLION, 100, 0.30, ad, "Samsung", "Motorola", "Apple", "Blackberry", "HTC", "LG", "Siemens", "Alcatel");

//        ad = getAd(AdType.ZU);
//        generateAd("data/extramobi/charger-net/", "direct/chargersAllSpb.xls", AdRegion.SPB, 100, 0.33, ad, "All");
//        generateAd("data/extramobi/charger-net/", "direct/chargersAllMoscow.xls", AdRegion.MOSCOW, 100, 0.33, ad, "All");
//        generateAd("data/extramobi/charger-net/", "direct/chargersAllRegion.xls", AdRegion.REGION_WITH_MILLION, 100, 0.33, ad, "All");

//        WritableSheet sheet = createAdsWorkbookAndSheet("direct/accumsNokSESpb.xls");
//        generateAccums("Nokia", false, false, 99, sheet, 0.25);
//        generateAccums("Sony Ericsson", false, false, 99, sheet, 0.25);
//        workbookWriteAndClose();
//
//        sheet = createAdsWorkbookAndSheet("direct/accumsNokSERegion.xls");
//        generateAccums("Nokia", true, false, 99, sheet, 0.2);
//        generateAccums("Sony Ericsson", true, false, 99, sheet, 0.2);
//        workbookWriteAndClose();
//
//        sheet = createAdsWorkbookAndSheet("direct/accumsSamMotAppBlackHTCLGSpb.xls");
//        generateAccums("Samsung", false, false, 100, sheet, 0.33);
//        generateAccums("Motorola", false, false, 99, sheet, 0.33);
//        generateAccums("Apple", false, false, 100, sheet, 0.33);
//        generateAccums("BlackBerry", false, false, 99, sheet, 0.33);
//        generateAccums("HTC", false, false, 99, sheet, 0.33);
//        generateAccums("LG", false, false, 100, sheet, 0.33);
//        workbookWriteAndClose();
//
//        sheet = createAdsWorkbookAndSheet("direct/accumsSamMotAppBlackHTCLGRegion.xls");
//        generateAccums("Samsung", true, true, 100, sheet, 0.26);
//        generateAccums("Motorola", true, true, 99, sheet, 0.26);
//        generateAccums("Apple", true, true, 100, sheet, 0.26);
//        generateAccums("BlackBerry", true, true, 99, sheet, 0.26);
//        generateAccums("HTC", true, true, 99, sheet, 0.26);
//        generateAccums("LG", true, true, 100, sheet, 0.26);
//        workbookWriteAndClose();

//        generateAccumsNokiaSPbAds(reader, sheet);
//        BufferedReader reader = new BufferedReader(new FileReader("data/body/body.nokia.txt"));
//        generateBodies("Nokia", sheet, false, false);
//        generateBodies("Samsung", sheet, false, false);
//        generateBodies("Sony Ericsson", sheet, false, false);

//        generateBodiesNokiaRegionsAds(reader, sheet, 0.15);
//        reader = new BufferedReader(new FileReader("data/body/body.samsung.txt"));
//        generateBodiesSamsungSPbAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/body/body.se.txt"));
//        generateBodiesSonyEricssonSPbAds(reader, sheet);
//        generateBodiesSamsungSPbAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/accum.htc.txt"));
//        reader = new BufferedReader(new FileReader("data/accum.apple.txt"));
//        reader = new BufferedReader(new FileReader("data/accum.blackberry.txt"));
//        reader = new BufferedReader(new FileReader("data/accum.samsung.txt"));
//        generateAccumsNokiaRegionsAds(reader, sheet, 0.15);
//        reader = new BufferedReader(new FileReader("data/accums/accum.se.txt"));
//        generateAccumsSonyEricssonAds(reader, sheet, true);
//        reader = new BufferedReader(new FileReader("data/accums/accum.samsung.txt"));
//        generateAccumsSamsungAds(reader, sheet, true);
//        reader = new BufferedReader(new FileReader("data/accums/accum.se.txt"));
//        generateAccumsSonyEricssonAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/accum.lg.txt"));
//        generateAccumsLGSPbAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/accums/accum.htc.txt"));
//        generateAccumsHTCSPbAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/accums/accum.motorola.txt"));
//        generateAccumsMotorolaAds(reader, sheet, true);
//        reader = new BufferedReader(new FileReader("data/accum.blackberry.txt"));
//        generateAccumsBlackberrySPbAds(reader, sheet);
//        reader = new BufferedReader(new FileReader("data/accum.apple.txt"));
//        generateAccumsAppleSPbAds(reader, sheet);

//        generateRepairAds(sheet, "Nokia", "nokia", 0.3);
//        generateRepairAds(sheet, "Motorola", "motorola", 0.3);
//        generateRepairAds(sheet, "lg", "lg", 0.3);
//        generateRepairAds(sheet, "Sony Ericsson", "sony-ericsson", 0.3);

//        generateRepairAds(sheet, "Samsung", "samsung", 0.3);
//        generateRepairAds(sheet, "HTC", "htc", 0.3);
//        generateRepairAds(sheet, "Blackberry", "blackberry", 0.3);
//        generateRepairAds(sheet, "Apple", "iphone", 0.3);

//        BufferedReader reader = new BufferedReader(new FileReader("data/chargers/charger.nokia.orig.txt"));
//        generateChargersAds("Nokia", reader, sheet, false, 0.2);
//        reader = new BufferedReader(new FileReader("data/chargers/car.charger.nokia.txt"));
//        generateChargersNokiaSPbAds(reader, sheet, true);
//        reader = new BufferedReader(new FileReader("data/chargers/charger.samsung.txt"));
//        generateChargersSamsungSPbAds(reader, sheet);

//        generateMatsSPbAds("newxls/ПРАЙС на ПУ ковры салона30.xls", sheet);
//        generateBagMatsSPbAds("newxls/ПРАЙС на ПУ поддоны30.xls", sheet);


    }


    private static void generateAvitoAds(String inPath, String outPath, AdRegion region, int minCount, double price, String... brands) throws BiffException, IOException, WriteException {
        Ad ad = getAd(AdType.AVITO_ACCUM);
        generateAd(AVITO_AD_URL, inPath, outPath, region, minCount, price, ad, brands);
    }

    public static Ad getAd(AdType type) {
        Ad ad = new Ad();
        if (type.equals(AdType.ACCUM)) {
            ad.directHeaderSPb = "Купить батарею %s в СПб!";
            ad.directHeaderMoscow = "Купить аккумулятор %s в Москве!";
            ad.directHeaderRegion = "Заказать аккум-р %s из СПб!";

            ad.directTextSPb = "Оригинальный аккумулятор %s? В наличии у м.Сенная, сегодня скидки!";
            ad.directTextMoscow = "Купить аккумулятор %s в Москве! Самовывоз пл. Ильича, сегодня скидки!";
            ad.directTextRegion = "Оригинальный аккумулятор %s! Доставка почтой РФ ускоренная, скидки!";

            ad.validateHeaderMap.put("Motorola", "Moto.");
            ad.validateHeaderMap.put("Moto. ", "");
            ad.validateHeaderMap.put("Sony Ericsson", "Sony E.");
            ad.validateHeaderMap.put("Sony E.", "Sony");
            ad.validateHeaderMap.put("Sony ", "");
            ad.validateHeaderMap.put("Samsung ", "Sams. ");
            ad.validateHeaderMap.put("Sams. ", "");
            ad.validateHeaderMap.put("%s ", "");
            ad.validateHeaderMap.put("аккум-р", "аккум.");
            ad.validateHeaderMap.put("аккум.", "акк.");
            ad.validateTextMap.put("Sony Ericsson", "Sony E.");
            ad.validateTextMap.put("Sony E.", "Sony");
            ad.validateTextMap.put("Sony ", "");
            ad.validateTextMap.put("Оригинальный", "Оригин-й");
            ad.validateTextMap.put("Оригин-й", "Ориг.");
            ad.validateTextMap.put("%s ", "");
            ad.validateTextMap.put("Москве", "Мск");
            ad.urlPostfix = "accumulator ";
            ad.phraseList.add("Аккумулятор");
            ad.phraseList.add("Батарея");
            ad.phraseList.add("АКБ");
            ad.phraseList.add("Батарейка");
            ad.stopPhrases = ACCUMS_STOP_PHRASES;
        } else if (type.equals(AdType.AVITO_ACCUM)) {
            ad.directHeaderSPb = "Распродажа батарей %s!";
            ad.directTextSPb = "Оригинальный аккумулятор %s есть в наличии, сегодня всего 350р.!";
            ad.validateHeaderMap.put("Motorola", "Moto");
            ad.validateHeaderMap.put("Moto", "");
            ad.validateHeaderMap.put("Sony Ericsson", "Sony");
            ad.validateTextMap.put("Sony Ericsson", "Sony");
            ad.validateTextMap.put("всего ", "");
            ad.phraseList.add("Аккумулятор");
            ad.phraseList.add("Батарея");
            ad.phraseList.add("АКБ");
            ad.phraseList.add("Батарейка");
            ad.stopPhrases = ACCUMS_STOP_PHRASES;
        } else if (type.equals(AdType.CHARGER)) {
            ad.directHeaderSPb = "Купить зарядку %s в СПб!";
            ad.directHeaderMoscow = "Купить зарядку %s в Москве!";
            ad.directHeaderRegion = "Заказать зарядку %s из СПб!";

            ad.directTextSPb = "Оригинальное зарядное %s! В наличии у м.Сенная, доставка %sр.";
            ad.directTextMoscow = "Купить оригинальное зарядное %s в Москве!Самовывоз пл. Ильича, доставка %sр.";
            ad.directTextRegion = "Оригинальное зарядное %s! Доставка почтой РФ %sр. ускоренная!";

            ad.validateHeaderMap.put("зарядку", "ЗУ");
            ad.validateHeaderMap.put("Sony Ericsson", "Sony Er.");
            ad.validateHeaderMap.put("Sony Er.", "Sony");
            ad.validateHeaderMap.put("BlackBerry", "Black.");
            ad.validateHeaderMap.put("Motorola", "Moto.");
            ad.validateHeaderMap.put("%s ", "");
            ad.validateTextMap.put("Оригинальное", "Оригин.");
            ad.validateTextMap.put("оригинальное", "ориг.");
            ad.validateTextMap.put("Sony Ericsson", "Sony Er.");
            ad.validateTextMap.put("Sony Er.", "Sony");
            ad.validateTextMap.put("BlackBerry", "Black.");
            ad.validateTextMap.put("Motorola", "Moto.");
            ad.validateTextMap.put("зарядное", "ЗУ");
            ad.validateTextMap.put("%s ", "");
            ad.urlPostfix = "setevoe_zaryadnoe_ustroystvo ";
            ad.phraseList.add("Зарядное");
            ad.phraseList.add("Зарядка");
            ad.phraseList.add("АЗУ");
            ad.phraseList.add("СЗУ");
            ad.phraseList.add("ЗУ");
            ad.stopPhrases = CHARGERS_STOP_PHRASES;
        }
        return ad;
    }

    private static void generateAd(String url, String inPath, String outPath, AdRegion region, int minCount, double maxCPC, Ad ad, String... brands) throws IOException, WriteException, BiffException {
        WritableSheet sheet = createAdsWorkbookAndSheet(outPath);
        List<Item> items = Item.parseItemsXlsDir(inPath, brands);
        for (String brandName : brands) {
            List<String> processedModels = new ArrayList<String>();
            for (Item item : items) {
                if (item.brand.equalsIgnoreCase(brandName)) {
                    if (item.count < minCount) {
                        System.out.println(outPath + " rejected by count: " + item.name + "; minCount=" + minCount);
                        continue;
                    }
                    DirectAd directAd = new DirectAd();
                    String name = DirectHelper.filterName(item.name);
                    String[] nameParts = name.split(",");
                    if (name.length() != 0) {
                        if (nameParts.length > 1) {
                            if (nameParts[0].contains(brandName)) {
                                name = nameParts[0];
                            } else {
                                name = brandName + " " + nameParts[0];
                            }
                        }
                        filterAdsAndRegions(region, ad, directAd, name);
                        validateAd(directAd, ad, brandName);
                        directAd.maxPrice = maxCPC;
                        directAd.url = url;
                        if (url == null) {
                            directAd.url = commonProps.get("common.product-url") + DataHelper.codeInURL(ad.urlPostfix + item.name);
                        }
                        directAd.count = ++adCount;
                        directAd.adId = adCount;
                        if (nameParts.length > 1) {
                            for (String phrase : nameParts) {
                                directAd.phraseId = ++phraseCount;
                                directAd.phrase = filterPhrase(phrase + ACCUMS_STOP_PHRASES);
                                ExcelHelper.addAd(directAd, sheet, getCount());
                            }
                        } else {
                            if (!brandName.equals("Apple")) {
                                directAd.phraseId = ++phraseCount;
//                                directAd.phrase = filterPhrase(item.props.get("Модель") + ACCUMS_STOP_PHRASES);
                                directAd.phrase = filterPhrase(name.replace(brandName, "").trim() + ACCUMS_STOP_PHRASES);
                                ExcelHelper.addAd(directAd, sheet, getCount());
                            }
                        }
                    }
                    for (String model : item.models) {
                        String trimModel = DirectHelper.trimModel(model, brandName);
                        if (url == null) {
                            directAd.url = commonProps.get("common.product-url") + DataHelper.codeInURL(ad.urlPostfix + item.brand + " " + model);
                        }
                        if (processedModels.contains(trimModel)) {
                            System.out.println("rejected by model : " + trimModel);
                            continue;
                        }
                        processedModels.add(trimModel);
                        nameParts = trimModel.split(",");
                        if (nameParts.length > 1) {
                            name = brandName + " " + nameParts[0];
                        } else {
                            name = brandName + " " + trimModel;
                        }
                        filterAdsAndRegions(region, ad, directAd, name);
                        directAd.count = ++adCount;
                        directAd.adId = adCount;
                        directAd.maxPrice = maxCPC;
                        validateAd(directAd, ad, brandName);
                        if (nameParts.length > 1) {
                            for (String phrase : nameParts) {
                                addPhrases(sheet, directAd, phrase, ad, brandName);
                            }
                        } else {
                            addPhrases(sheet, directAd, trimModel, ad, brandName);
                        }
                    }
                }
            }
        }
        workbookWriteAndClose();
        adCount = phraseCount = 1;
    }

    private static String filterPhrase(String phrase) {
        phrase = phrase.replaceAll("\\(", "");
        phrase = phrase.replaceAll("\\)", "");
        phrase = phrase.replaceAll("тонкая", "");
        phrase = phrase.replaceAll("толстая", "");
        return phrase;
    }

    private static void filterAdsAndRegions(AdRegion region, Ad ad, DirectAd directAd, String name) {
        directAd.region = region.regionYandex;
        if (region.equals(AdRegion.SPB)) {
            directAd.header = ad.filterDirectHeaderSPb(name);
            directAd.text = ad.filterDirectTextSPb(name, SHIPPING_COST_SPB + "");
        } else if (region.equals(AdRegion.MOSCOW)) {
            directAd.header = ad.filterDirectHeaderMoscow(name);
            directAd.text = ad.filterDirectTextMoscow(name, SHIPPING_COST_MOSCOW + "");
        } else { // region
            directAd.header = ad.filterDirectHeaderRegion(name);
            directAd.text = ad.filterDirectTextRegion(name, SHIPPING_COST_REGION + "");
        }
    }

    public static boolean belongBrandsList(String brandName, String... brands) {
        return Item.isInBrandsList(brandName, brands);
    }

    private static void workbookWriteAndClose() throws IOException, WriteException {
        workbook.write();
        workbook.close();
        count = 1;
    }

    private static int getCount() {
        return ++count;
    }

    private static WritableSheet createAdsWorkbookAndSheet(String path) throws IOException, WriteException {
        File xlsFile = new File(path);
        WorkbookSettings s = new WorkbookSettings();
        s.setLocale(new Locale("ru", "RU"));
        s.setEncoding("KOI8-R");
        workbook = Workbook.createWorkbook(xlsFile, s);
        workbook.createSheet("Тексты", 0);
        WritableSheet sheet = workbook.getSheet(0);
        ExcelHelper.addDirectHeader(sheet);
        return sheet;
    }

    /*
    private static void generateBodiesSonyEricssonSPbAds(BufferedReader reader, WritableSheet sheet) throws WriteException, IOException {
        String line;
        List<String> processedList = new ArrayList<String>();
        while ((line = reader.readLine()) != null) {
            if (line.contains("﻿")) {
                line = line.replace("﻿", "");
            }
            Body body = Body.parseBody(line, 2);

            String name = body.brandName + " " + body.model;

            String trimModel = DirectHelper.trimModel(name, "sony ericsson");

            if (body.count < 1) {
                System.out.println("rejected by adCount: " + name);
                continue;
            }
            adCount++;
            phraseCount++;
            DirectAd ad = new DirectAd();
            ad.header = "Купите корпус " + name + " за " + body.price + "р.";
            ad.text = "Корпус под оригинал " + trimModel + " за " + body.price + "р. Бесплатная доставка в СПб сегодня";
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace(name, trimModel);
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Sony Ericsson", "SE");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("за ", "");
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("в СПб", "СПб");
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("СПб", "");
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("оригинал", "оригин.");
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("оригин.", "ориг.");
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("ориг.", "ор.");
            }
            checkLength(ad);
            ad.url = BODY_BASE_URL + name.replaceAll("\\s", "_").toLowerCase();
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = "корпус " + trimModel.toLowerCase().replace("sony ericsson ", "");

            if (!processedList.contains(ad.phrase)) {
                ExcelHelper.addAd(ad, sheet);
                processedList.add(ad.phrase);
            }
        }
    }

    private static void generateBodiesSamsungSPbAds(BufferedReader reader, WritableSheet sheet) throws WriteException, IOException {
        String line;
        List<String> processedList = new ArrayList<String>();
        while ((line = reader.readLine()) != null) {
            if (line.contains("﻿")) {
                line = line.replace("﻿", "");
            }
            Body body = Body.parseBody(line, 2);

            String name = body.brandName + " " + body.model;

            String trimModel = DirectHelper.trimModel(name, "Samsung");

            if (body.count < 1) {
                System.out.println("rejected by adCount: " + name);
                continue;
            }
            adCount++;
            phraseCount++;
            DirectAd ad = new DirectAd();
            ad.header = "Купите корпус " + name + " за " + body.price + "р";
            ad.text = "Корпус под оригинал " + trimModel + " за " + body.price + "р. Бесплатная доставка в СПб сегодня";
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("за ", "");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace(name, trimModel);
            }
            if (ad.text.length() > 33) {
                ad.text = ad.text.replace("в СПб", "СПб");
            }
            checkLength(ad);
            ad.url = BODY_BASE_URL + name.replaceAll("\\s", "_").toLowerCase();
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = "корпус " + trimModel.toLowerCase().replace("samsung ", "");

            if (!processedList.contains(ad.phrase)) {
                ExcelHelper.addAd(ad, sheet);
                processedList.add(ad.phrase);
            }
        }
    }
    */
/*
    private static void generateMatsSPbAds(String priceFile, WritableSheet sheet) throws BiffException, IOException, WriteException {
        List<Mat> mats = ExcelParser.parseMatPrice(new File(priceFile), "", 1);
        List<String> processedModels = new ArrayList<String>();
        for (Mat mat : mats) {
            adCount++;
            phraseCount++;
            String matName = mat.name;
//            if (matName.contains("TagAZ") || matName.contains("UAZ") || matName.contains("ZAZ") || matName.contains("VAZ") || matName.contains("GAZ") || matName.contains("универс")) {
//                continue;
//            }
            if (mat.name.contains("(")) {
                matName = mat.name.substring(0, mat.name.indexOf("(")).trim();
                matName = Mat.trimModel(matName);
            }
//            mat.name =
            if (processedModels.contains(matName)) {
                continue;
            }
            processedModels.add(matName);
            DirectAd ad = new DirectAd();
            ad.header = "Купите коврик в салон " + matName;
            ad.text = "Оригин. коврик в салон " + matName + " за " + mat.price + "р. Доставка 250р.";
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Купите к", "К");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Chevrolet", "Chevy");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Chrysler-PT", "Chrysler");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Picasso", "Pic.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Caravan", "Carav.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Connect", "Conn.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Discovery", "Dis");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Freelander", "Free");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Land Rover Range Rover", "Range Rover");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Outlander", "Out.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Pajero", "Paj.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Mitsubishi", "Mitsub.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Mercedes-Benz", "Mercedes");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Almera Classic", "Almera Clas");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Tepee", "Te");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Эконом", "Эк");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Vitara", "Vit");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Verso", "V-o");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Golf Plus", "Golf P.");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Passat", "Pas");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Toyota Land Cruise", "Toyota Cruise");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("Toyota Cruiser Prado", "Toyota Cruiser Pra");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Доставка 200р.", "");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Оригин. к", "К");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Sport", "S-t");
            }
//            if (ad.text.length() > 75) {
//                ad.text = ad.text.replace("Freelander", "Free-r");
//            }
            checkLength(ad);
            ad.url = MAT_BASE_URL + DataGenerator.translateToEng(mat.name.replaceAll("\\s", "_").toLowerCase().replaceAll("\\(", "").replaceAll("\\)", ""));
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = "коврик " + matName;
            ExcelHelper.addAd(ad, sheet);
        }
    }
 */
    /*
        private static void generateChargersSamsungSPbAds(BufferedReader reader, WritableSheet sheet) throws WriteException, IOException {
            String line;
            List<String> processedModels = new ArrayList<String>();
            while ((line = reader.readLine()) != null) {
                if (line.contains("﻿")) {
                    line = line.replace("﻿", "");
                }
                Charger charger = Charger.parseCharger(line, 2, false);
                if (charger.count < 1) {
                    System.out.println("rejected by adCount: " + charger.name);
                    continue;
                }
                adCount++;
                phraseCount++;
                DirectAd ad = new DirectAd();
                ad.header = "Купите зарядное уст-во " + charger.name;
                ad.text = "Оригин. зарядное уст-во " + charger.name + " за " + charger.price + "р. Бесплатная доставка в СПб сегодня";
                ad.maxPrice = AD_MAX_PRICE;
                if (ad.header.length() > 33) {
                    ad.header = ad.header.replace("Samsung ", "");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("Samsung ", "");
                }
                if (ad.header.length() > 33) {
                    ad.header = ad.header.replace("уст-во ", "");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("уст-во ", "");
                }
                checkLength(ad);
                ad.url = CHARGER_NETWORK_BASE_URL + charger.name.replaceAll("\\s", "_").toLowerCase();
                ad.count = adCount;
                ad.adId = adCount;
                ad.phraseId = phraseCount;
                ad.phrase = charger.model;
                ExcelHelper.addAd(ad, sheet);
                for (String model : charger.models) {
                    String trimModel = DirectHelper.trimModel(model, "Samsung");
                    if (processedModels.contains(trimModel)) {
                        continue;
                    }
                    adCount++;
                    phraseCount++;
                    processedModels.add(trimModel);
                    ad.header = "Купите зарядное уст-во Samsung " + trimModel;
                    ad.text = "Оригин. зарядное уст-во Samsung " + trimModel + " за " + charger.price + "р. Бесплатная доставка сегодня";
                    ad.count = adCount;
                    ad.count = adCount;
                    ad.adId = adCount;
                    ad.phraseId = phraseCount;
                    if (ad.header.length() > 33) {
                        ad.header = ad.header.replace("уст-во ", "");
                    }
                    if (ad.text.length() > 75) {
                        ad.text = ad.text.replace("уст-во ", "");
                    }
                    checkLength(ad);
                    generateChargerPhrases(sheet, ad, trimModel);
                }
            }
        }
    */
/*
    private static void generateChargersAds(String brandName, BufferedReader reader, WritableSheet sheet, boolean region, double maxCPC) throws IOException, WriteException {
        List<String> processedModels = new ArrayList<String>();
        boolean auto = false;
        List<Charger> chargers = Charger.parseChargers(brandName, 1, auto);
        for (Charger charger : chargers) {
            if (charger.count < 1) {
                System.out.println("rejected by adCount: " + charger.name);
                continue;
            }

            adCount++;
            phraseCount++;
            DirectAd ad = new DirectAd();
            ad.header = "Купите зарядное уст-во " + charger.name;
            ad.text = "Оригин. зарядное уст-во " + charger.name + " за " + charger.price + "р. Бесплатная доставка в СПб сегодня";
            ad.maxPrice = maxCPC;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("уст-во ", "");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("уст-во ", "");
            }
            checkLength(ad);
//            String baseUrl = auto ? CHARGER_AUTO_BASE_URL : CHARGER_NETWORK_BASE_URL;
//            ad.url = baseUrl + charger.name.replaceAll("\\s", "_").toLowerCase();
            ad.url = charger.url;
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = charger.model;
            ExcelHelper.addAd(ad, sheet, getCount());
            adCount++;
            phraseCount++;
            ad.adId = adCount;
//            if (charger.model.contains("E")) {
//                ad.phrase = charger.model.replace("E", "");
//                ad.header = ad.header.replace("E", "");
//                ad.text = ad.text.replace("E", "");
//                ad.phraseId = phraseCount;
//                ExcelHelper.addAd(ad, sheet);
//            }
            int i = 0;
            for (String model : charger.models) {
                String url = charger.modelURLs.get(i++);
                String trimModel = DirectHelper.trimModel(model, brandName);
                String[] nameParts = trimModel.split(",");
                String name;
                if (nameParts.length > 1) {
                    name = brandName + " " + nameParts[0];
                } else {
                    name = brandName + " " + trimModel;
                }
                if (processedModels.contains(trimModel)) {
                    continue;
                }
                adCount++;
                phraseCount++;
                processedModels.add(trimModel);
                ad.header = "Купите зарядное уст-во " + name;
                ad.text = "Оригин. зарядное уст-во " + name + " за " + charger.price + "р. Бесплатная доставка сегодня";
                ad.count = adCount;
                ad.count = adCount;
                ad.adId = adCount;
                ad.phraseId = phraseCount;
                if (ad.header.length() > 33) {
                    ad.header = ad.header.replace("уст-во ", "");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("уст-во ", "");
                }
                checkLength(ad);
                if (nameParts.length > 1) {
                    for (String phrase : nameParts) {
                        generateChargerPhrases(sheet, ad, phrase);
                    }
                } else {
                    generateChargerPhrases(sheet, ad, trimModel);
                }
            }
        }
    }
*/

    private static void generateChargerPhrases(WritableSheet sheet, DirectAd ad, String trimModel) throws WriteException {
        ad.phrase = "зарядка " + trimModel;
        ExcelHelper.addAd(ad, sheet, getCount());
        phraseCount++;
        ad.count = adCount;
        ad.phraseId = phraseCount;
        ad.phrase = "зарядное " + trimModel;
        ExcelHelper.addAd(ad, sheet, getCount());
    }

    /**
     * private static void generateChargersNokiaRegionsAds(BufferedReader reader, WritableSheet sheet, boolean auto) throws IOException, WriteException {
     * String line;
     * List<String> processedModels = new ArrayList<String>();
     * while ((line = reader.readLine()) != null) {
     * if (line.contains("﻿")) {
     * line = line.replace("﻿", "");
     * }
     * Charger charger = Charger.parseCharger(line);
     * if (charger.count < 1) {
     * System.out.println("rejected by adCount: " + charger.name);
     * continue;
     * }
     * adCount++;
     * phraseCount++;
     * DirectAd ad = new DirectAd();
     * ad.header = "Купите зарядное уст-во " + charger.name;
     * ad.text = "Оригин. зарядное уст-во " + charger.name + " за " + charger.price + "р. Бесплатная доставка в СПб сегодня";
     * ad.maxPrice = AD_MAX_PRICE;
     * if (ad.header.length() > 33) {
     * ad.header = ad.header.replace("уст-во ", "");
     * }
     * if (ad.text.length() > 75) {
     * ad.text = ad.text.replace("уст-во ", "");
     * }
     * checkLength(ad);
     * String baseUrl = auto ? CHARGER_AUTO_BASE_URL : CHARGER_NETWORK_BASE_URL;
     * ad.url = baseUrl + charger.name.replaceAll("\\s", "_").toLowerCase();
     * ad.count = adCount;
     * ad.adId = adCount;
     * ad.phraseId = phraseCount;
     * ad.phrase = charger.model;
     * ExcelHelper.addAd(ad, sheet);
     * adCount++;
     * phraseCount++;
     * ad.adId = adCount;
     * if (charger.model.contains("E")) {
     * ad.phrase = charger.model.replace("E", "");
     * ad.header = ad.header.replace("E", "");
     * ad.text = ad.text.replace("E", "");
     * ad.phraseId = phraseCount;
     * ExcelHelper.addAd(ad, sheet);
     * }
     * for (String model : charger.devices) {
     * String trimModel = DirectHelper.trimModel(model, "Nokia");
     * if (processedModels.contains(trimModel)) {
     * continue;
     * }
     * adCount++;
     * phraseCount++;
     * processedModels.add(trimModel);
     * ad.header = "Купите зарядное уст-во Nokia " + trimModel;
     * ad.text = "Оригин. зарядное уст-во Nokia " + trimModel + " за " + charger.price + "р. Бесплатная доставка сегодня";
     * ad.count = adCount;
     * ad.count = adCount;
     * ad.adId = adCount;
     * ad.phraseId = phraseCount;
     * if (ad.header.length() > 33) {
     * ad.header = ad.header.replace("уст-во ", "");
     * }
     * if (ad.text.length() > 75) {
     * ad.text = ad.text.replace("уст-во ", "");
     * }
     * checkLength(ad);
     * ad.phrase = "зарядка " + trimModel;
     * ExcelHelper.addAd(ad, sheet);
     * phraseCount++;
     * ad.count = adCount;
     * ad.phraseId = phraseCount;
     * ad.phrase = "зарядное " + trimModel;
     * ExcelHelper.addAd(ad, sheet);
     * }
     * }
     * }
     */

    private static void generateBodies(String brandName, WritableSheet sheet, boolean region, boolean million) throws WriteException, BiffException, IOException {
//        List<String> processedList = new ArrayList<String>();
        List<Body> bodies = Body.parseBodies(brandName, 1);
        for (Body body : bodies) {
            String name = body.model;

            String trimModel = DirectHelper.trimModel(name, brandName);

            if (body.count < 100) {
                System.out.println("rejected by adCount: " + name);
                continue;
            }

            String[] nameParts = trimModel.split(",");
            if (nameParts.length > 1) {
                name = nameParts[0];
            } else {
                name = trimModel;
            }

//            adCount++;
//            phraseCount++;
            DirectAd ad = new DirectAd();
            ad.header = "Купить корпус " + name.replace("Sony Ericsson", "SE") + " за " + body.price + "р";
            ad.text = "Качественный корпус " + name.replace("Sony Ericsson", "SE") + " " + body.price + "р. Самовывоз м.Восстания, доставка " + SHIPPING_COST_SPB + "р.";
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("за ", "- ");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("- ", "");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace(" " + body.price + "р", "");
            }
            checkLength(ad);
//            ad.url = BODY_BASE_URL + name.replaceAll("\\s", "_").toLowerCase();
            ad.url = body.url;
            ad.count = adCount;
            ad.adId = adCount++;
//            ad.phraseId = phraseCount;
//            ad.phrase = "корпус " + trimModel.toLowerCase().replace("nokia ", "");

//            String[] nameParts = name.split(",");
            if (nameParts.length > 1) {
                for (String phrase : nameParts) {
                    if (phrase.contains("XpressMusic")) {
                        continue;
                    }
                    ad.phraseId = ++phraseCount;
                    ad.phrase = "корпус " + phrase.replace(brandName + " ", "") + BODIES_STOP_PHRASES;
                    ExcelHelper.addAd(ad, sheet, getCount());
                }
            } else {
                ad.phraseId = ++phraseCount;
                ad.phrase = "корпус " + name.replace(brandName + " ", "") + BODIES_STOP_PHRASES;
                ExcelHelper.addAd(ad, sheet, getCount());
            }

//            if (!processedList.contains(ad.phrase)) {
//                ExcelHelper.addAd(ad, sheet);
//                processedList.add(ad.phrase);
//            }
        }
    }

    private static void generateBodiesNokiaRegionsAds(BufferedReader reader, WritableSheet sheet, double maxClickPrice) throws WriteException, BiffException, IOException {
        String line;
        List<String> processedList = new ArrayList<String>();
        while ((line = reader.readLine()) != null) {
            if (line.contains("﻿")) {
                line = line.replace("﻿", "");
            }
            Body body = Body.parseBody(line, 2);

            String name = body.brandName + " " + body.model;

            String trimModel = DirectHelper.trimModel(name, "Nokia");

            if (body.count < 1) {
                System.out.println("rejected by adCount: " + name);
                continue;
            }
            adCount++;
            phraseCount++;
            DirectAd ad = new DirectAd();
            ad.region = "Центр, -Москва и область, -Воронеж и область, Северо-Запад, -Санкт-Петербург и Ленинградская область, Поволжье, -Нижний Новгород и область, -Пермский край, -Уфа, -Казань, -Самара и область, -Саратов, Юг, -Волгоград и область, -Краснодар, -Ростов-на-Дону и область, Сибирь, -Красноярск, -Новосибирск и область, -Омск и область, Дальний Восток, -Хабаровский край, Урал, -Екатеринбург и Свердловская область, -Челябинск и область";
            ad.header = "Закажите корпус " + name + " из СПБ";
            ad.text = "Корпус под оригинал " + trimModel + " за " + body.price + "р. Доставка почтой 250р. ускоренная";
            ad.maxPrice = maxClickPrice;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace(" из", ",");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace(name, trimModel);
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace(" за ", "-");
            }
            checkLength(ad);
            ad.url = BODY_BASE_URL + name.replaceAll("\\s", "_").toLowerCase();
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = "корпус " + trimModel.toLowerCase().replace("nokia ", "");

            if (!processedList.contains(ad.phrase)) {
                ExcelHelper.addAd(ad, sheet, getCount());
                processedList.add(ad.phrase);
            }
        }
    }

    /*
    private static void generateAccumsMotorolaAds(BufferedReader reader, WritableSheet sheet, boolean region) throws WriteException, IOException {
        List<String> processedModels = new ArrayList<String>();
        String line;
        while ((line = reader.readLine()) != null) {
            if (line.contains("﻿")) {
                line = line.replace("﻿", "");
            }
            Accum accum = Accum.parseAccum(line, 1);
            if (accum.count < 100) {
                System.out.println("rejected by adCount: " + accum.name);
                continue;
            }
            adCount++;
            phraseCount++;
            String[] fields = accum.name.split(" ");
            String shortName = fields[fields.length - 1];
            DirectAd ad = new DirectAd();
            if (region) {
                ad.region = "Центр, -Москва и область, Северо-Запад, -Санкт-Петербург и Ленинградская область, Поволжье, Юг, Сибирь, Дальний Восток, Урал";
                ad.header = "Закажите аккумулятор " + accum.name;
                ad.text = "Оригинальный аккум-р " + accum.name + " за " + accum.price + "р. Доставка почтой 250р. из СПб";
            } else {
                ad.header = "Купите аккумулятор " + accum.name;
                ad.text = "Оригинальный аккум-р " + accum.name + " за " + accum.price + "р. Бесплатная доставка в СПб сегодня";
            }
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("аккумулятор", "аккумул-р");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace(" 250р.", "");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace(" СПб", "");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Оригинальный", "Оригин.");
            }
            checkLength(ad);
            ad.url = ACCUM_BASE_URL + accum.name.replaceAll("\\s", "_").toLowerCase();
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = shortName.toLowerCase();

            ExcelHelper.addAd(ad, sheet);
            for (String model : accum.devices) {
                String trimModel = DirectHelper.trimModel(model, "Motorola");
                if (processedModels.contains(trimModel)) {
                    continue;
                }
                adCount++;
                phraseCount++;
                processedModels.add(trimModel);
                if (region) {
                    ad.header = "Закажите аккумулятор Motorola " + trimModel;
                    ad.text = "Оригинальный аккум-р Motorola " + trimModel + " за " + accum.price + "р. Доставка почтой 250р. из СПб";
                } else {
                    ad.header = "Купите аккумулятор Motorola " + trimModel;
                    ad.text = "Оригинальный аккум-р Motorola " + trimModel + " за " + accum.price + "р. Бесплатная доставка в СПб сегодня";
                }
                ad.url = ACCUM_BASE_URL + "motorola_" + model.replaceAll("\\s", "_").toLowerCase();
                ad.count = adCount;
                ad.adId = adCount;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace(" 250р.", "");
                }
                if (ad.header.length() > 33) {
                    ad.header = ad.header.replace("аккумулятор", "аккум.");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace(" за", "");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace(" СПб", "");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("Оригинальный", "Оригинал.");
                }
                checkLength(ad);
                ad.phrase = "аккумулятор " + trimModel;
                ExcelHelper.addAd(ad, sheet);
                phraseCount++;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                ad.phrase = "батарея " + trimModel;
                ExcelHelper.addAd(ad, sheet);
                phraseCount++;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                ad.phrase = "батарейка " + trimModel;
                ExcelHelper.addAd(ad, sheet);
                phraseCount++;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                ad.phrase = "акб " + trimModel;
                ExcelHelper.addAd(ad, sheet);
            }
        }
    }

    private static void generateAccumsHTCSPbAds(BufferedReader reader, WritableSheet sheet) throws WriteException, IOException {
        List<String> processedModels = new ArrayList<String>();
        String line;
        while ((line = reader.readLine()) != null) {
            if (line.contains("﻿")) {
                line = line.replace("﻿", "");
            }
            Accum accum = Accum.parseAccum(line, 1);
            if (accum.count < 1) {
                System.out.println("rejected by adCount: " + accum.name);
                continue;
            }
            adCount++;
            phraseCount++;
//            String[] fields = accum.name.split(" ");
//            String shortName = fields[fields.length - 1];
            String shortName = DirectHelper.filterName(accum.name);

            DirectAd ad = new DirectAd();
            ad.header = "Купите аккумулятор " + shortName;
            ad.text = "Оригин. аккумулятор " + shortName + " за " + accum.price + "р. Бесплатная доставка в СПб сегодня";
            ad.maxPrice = AD_MAX_PRICE;
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("аккумулятор", "аккум.");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Оригинальный", "Оригинал.");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("Оригинал.", "Оригин.");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("аккумулятор", "аккум.");
            }
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace("в СПб ", "");
            }
            checkLength(ad);
            ad.url = ACCUM_BASE_URL + accum.name.replaceAll("\\s", "_").toLowerCase();
            ad.count = adCount;
            ad.adId = adCount;
            ad.phraseId = phraseCount;
            ad.phrase = shortName.toLowerCase();

            ExcelHelper.addAd(ad, sheet);
            for (String model : accum.devices) {
                String trimModel = DirectHelper.trimModel(model, "HTC");
                if (processedModels.contains(trimModel)) {
                    continue;
                }
                adCount++;
                phraseCount++;
                processedModels.add(trimModel);
                ad.header = "Купите аккумулятор HTC " + trimModel;
                ad.text = "Оригинальный аккумулятор HTC " + trimModel + " за " + accum.price + "р. Бесплатная доставка сегодня";
                ad.url = ACCUM_BASE_URL + "htc_" + model.replaceAll("\\s", "_").toLowerCase();
                ad.count = adCount;
                ad.adId = adCount;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                if (ad.header.length() > 33) {
                    ad.header = ad.header.replace("аккумулятор", "аккум.");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("Оригинальный", "Оригинал.");
                }
                if (ad.text.length() > 75) {
                    ad.text = ad.text.replace("Оригинал.", "Оригин.");
                }
                checkLength(ad);
                ad.phrase = "аккумулятор " + trimModel;
                ExcelHelper.addAd(ad, sheet);
                phraseCount++;
                ad.phraseId = phraseCount;
                ad.maxPrice = AD_MAX_PRICE;
                ad.phrase = "батарея " + trimModel;
                ExcelHelper.addAd(ad, sheet);
            }
        }
    }
  */

    private static void addPhrases(WritableSheet sheet, DirectAd directAd, String modelName, Ad ad, String brandName) throws WriteException {
        String brandNameTranslit = translit(brandName);
        if (brandName.equalsIgnoreCase("apple")) {
            brandName = "";
            brandNameTranslit = "";
        } else if (brandName.equalsIgnoreCase("sony ericsson")) {
            brandName = "sony";
        }
        for (String phrase : ad.phraseList) {
            directAd.phrase = filterPhrase(phrase + " " + brandName + " " + modelName + ad.stopPhrases);
            directAd.phraseId = ++phraseCount;
            ExcelHelper.addAd(directAd, sheet, getCount());
            String modelNameTranslit = translit(modelName);
            if (modelNameTranslit != null) {
                directAd.phrase = filterPhrase(phrase + " " + brandNameTranslit + " " + modelNameTranslit + ad.stopPhrases);
                directAd.phraseId = ++phraseCount;
                ExcelHelper.addAd(directAd, sheet, getCount());
            }
            if (brandNameTranslit != null) {
                directAd.phrase = filterPhrase(phrase + " " + brandNameTranslit + " " + modelName + ad.stopPhrases);
                directAd.phraseId = ++phraseCount;
                ExcelHelper.addAd(directAd, sheet, getCount());
            }
        }
    }

    /*
        private static void addAccumPhrases(WritableSheet sheet, DirectAd ad, String modelName) throws WriteException {
            ad.phrase = "аккумулятор " + modelName + ACCUMS_STOP_PHRASES;
            ad.phraseId = ++phraseCount;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = ++phraseCount;
            ad.phrase = "батарея " + modelName + ACCUMS_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = ++phraseCount;
            ad.phrase = "акб " + modelName + ACCUMS_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = ++phraseCount;
            ad.phrase = "батарейка " + modelName + ACCUMS_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());
        }
    */
    private static void validateAd(DirectAd directAd, Ad ad, String brandName) {
        for (String key : ad.validateHeaderMap.keySet()) {
            if (validateHeader(directAd, String.format(key, brandName), ad.validateHeaderMap.get(key))) {
                break;
            }
        }
        for (String key : ad.validateTextMap.keySet()) {
            if (validateText(directAd, String.format(key, brandName), ad.validateTextMap.get(key))) {
                break;
            }
        }
        checkLength(directAd);
    }

    /*
    private static void generateAccums(String brandName, boolean region, boolean millionCities, int minCount, WritableSheet sheet, double maxCPC) throws IOException, WriteException, BiffException {
        List<String> processedModels = new ArrayList<String>();
        List<Item> items = Item.initItems("data/extramobi/accum/");
        for (Item item : items) {
            if (item.brand.equals(brandName)) {
                if (item.count < minCount) {
                    System.out.println("rejected by count: " + item.name + "; minCount=" + minCount);
                    continue;
                }
                DirectAd ad = new DirectAd();
                String name = DirectHelper.filterName(item.name);
                String[] nameParts = name.split(",");
                if (nameParts.length > 1) {
                    name = brandName + " " + nameParts[0];
                }
                if (region) {
                    ad.region = millionCities ? REGIONS : REGIONS_WO_MILLIONS;
                    ad.header = "Заказать " + name + " из СПб!";
                    ad.text = "Оригинальный аккумулятор " + name + "! Доставка почтой РФ " + SHIPPING_COST_REGION + "р. ускоренная!";
                } else {
                    ad.header = "Купить аккумулятор " + name + " в СПб!";
                    ad.text = "Оригинальный аккумулятор " + name + "! В наличии у м.Сенная, доставка " + SHIPPING_COST_SPB + "р.";
                }
                validateHeader(ad, brandName + " ", "");
                validateHeader(ad, " в СПб", "");
                validateHeader(ad, "Sony Ericsson", "SE");
                validateHeader(ad, "аккумулятор", "аккум.");
                validateText(ad, "Sony Ericsson", "SE");
                validateText(ad, "Оригинальный", "Ориг.");
                ad.maxPrice = maxCPC;
                checkLength(ad);
                ad.url = commonProps.get("common.product-url") + DataHelper.codeInURL("accumulator " + item.name);
                ad.count = ++adCount;
                ad.adId = adCount;
                if (nameParts.length > 1) {
                    for (String phrase : nameParts) {
                        ad.phraseId = ++phraseCount;
                        ad.phrase = phrase + ACCUMS_STOP_PHRASES;
                        ExcelHelper.addAd(ad, sheet, getCount());
                    }
                } else {
                    if (!brandName.equals("Apple")) {
                        ad.phraseId = ++phraseCount;
                        ad.phrase = item.props.get("Модель") + ACCUMS_STOP_PHRASES;
                        ExcelHelper.addAd(ad, sheet, getCount());
                    }
                }
                int i = 0;
                for (String model : item.models) {
                    String trimModel = DirectHelper.trimModel(model, brandName);
                    ad.url = commonProps.get("common.product-url") + DataHelper.codeInURL("accumulator " + item.brand + " " + model);
                    if (processedModels.contains(trimModel)) {
                        continue;
                    }
                    processedModels.add(trimModel);
                    nameParts = trimModel.split(",");
                    if (nameParts.length > 1) {
                        name = brandName + " " + nameParts[0];
                    } else {
                        name = brandName + " " + trimModel;
                    }
                    if (region) {
                        ad.header = "Заказать аккум. " + name.replace("Apple ", "") + " из СПб!";
                        ad.text = "Оригинальный аккумулятор " + name + "! Доставка почтой РФ " + SHIPPING_COST_REGION + "р. ускоренная!";
                    } else {
                        ad.header = "Купить аккумулятор " + name + "!";
                        ad.text = "Оригинальный аккумулятор " + name + "! В наличии у м.Сенная, доставка " + SHIPPING_COST_SPB + "р.";
                    }
                    ad.count = ++adCount;
                    ad.adId = adCount;
                    validateHeader(ad, "Sony Ericsson", "SE");
                    validateHeader(ad, "из СПб!", "из СПб");
                    validateHeader(ad, " из СПб", "!");
                    validateHeader(ad, "Motorola", "Moto");
                    validateHeader(ad, "аккумулятор", "аккум.");
                    validateText(ad, "Sony Ericsson", "SE");
                    validateText(ad, "Оригинальный", "Ориг.");
                    validateText(ad, SHIPPING_COST_REGION + "р.", SHIPPING_COST_REGION + "р");
                    validateText(ad, "Motorola", "Moto");

                    checkLength(ad);

                    if (nameParts.length > 1) {
                        for (String phrase : nameParts) {
                            addAccumPhrases(sheet, ad, phrase);
                        }
                    } else {
                        addAccumPhrases(sheet, ad, trimModel);
                    }
                }
            }
        }
}
*/

    private static boolean validateText(DirectAd ad, String phraseToReplace, String newPhrase) {
        if (ad.text.length() > 75) {
            ad.text = ad.text.replace(phraseToReplace, newPhrase);
            return false;
        } else {
            return true;
        }
    }

    private static boolean validateHeader(DirectAd ad, String phraseToReplace, String newPhrase) {
        if (ad.header.length() > 33) {
            ad.header = ad.header.replace(phraseToReplace, newPhrase);
            return false;
        } else {
            return true;
        }
    }

    private static void checkLength(DirectAd ad) {
        if (ad.getHeaderLength() > 33 || ad.getTextLength() > 75) {
            throw new IllegalStateException(ad.header + ":" + ad.header.length() + ";" + ad.text.length() + ";" + ad.text);
        }
    }

    private static boolean checkProcessed(List<String> processedModels, String trimModel) {
        if (processedModels.contains(trimModel)) {
            System.out.println("rejected by model: " + trimModel);
            return true;
        }
        return false;
    }

    /*
        private static void generateRepairAds(WritableSheet sheet, String vendor, String urlPart, double maxCPC) throws WriteException, IOException, BiffException {
            List<String> processedModels = new ArrayList<String>();

            DirectAd ad = new DirectAd();
            ad.maxPrice = maxCPC;

            List<Templ> phones = Templ.parseTempls("phones", "remont", 1);

            for (Templ phone : phones) {
                if (!phone.vendor.equalsIgnoreCase(vendor)) {
                    continue;
                }
                int i = 0;
                for (String model : phone.models) {
                    String trimModel = DirectHelper.trimModel(model, vendor);
                    ad.url = phone.modelUrls.get(i++);
                    if (processedModels.contains(trimModel) || excludeModel(trimModel)) {
                        continue;
                    }
                    processedModels.add(trimModel);

                    if (phone.vendor.equalsIgnoreCase("sony ericsson")) {
                        phone.vendor = "SE";
                    }

                    String name;
                    String[] nameParts = trimModel.split(",");
                    if (nameParts.length > 1) {
                        name = phone.vendor + " " + nameParts[0];
                    } else {
                        name = phone.vendor + " " + trimModel;
                    }

                    ad.header = "Ремонт телефона " + name + " в центре СПб";
                    if (ad.header.length() > 33) {
                        ad.header = ad.header.replace("телефона ", "");
                    }
                    if (ad.header.length() > 33) {
                        ad.header = ad.header.replace("в центре СПб", "в центре");
                    }
                    if (ad.header.length() > 33) {
                        ad.header = ad.header.replace("в центре", "в СПб");
                    }
                    if (ad.header.length() > 33) {
                        ad.header = ad.header.replace(" в СПб", "");
                    }
                    ad.text = "Профессиональный ремонт " + name + " любой сложности! Доступные цены, гарантия!";
                    if (ad.text.length() > 75) {
                        ad.text = ad.text.replace("Проф. ремонт", "Ремонт");
                    }
                    if (ad.text.length() > 75) {
                        ad.text = ad.text.replace("Профессиональный", "Профес-ный");
                    }
                    if (ad.text.length() > 75) {
                        ad.text = ad.text.replace("Профес-ный", "Профес.");
                    }
                    checkLength(ad);

                    ad.adId = ad.count = adCount++;

                    if (nameParts.length > 1) {
                        for (String phrase : nameParts) {
                            generateRepairPhrases(sheet, ad, phrase);
                        }
                    } else {
                        generateRepairPhrases(sheet, ad, trimModel);
                    }
                }
            }

            ad.header = "Ремонт телефонов " + vendor + " в Санкт-Петербурге";
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("в Санкт-Петербурге", "в СПб");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace("телефонов ", "");
            }
            if (ad.header.length() > 33) {
                ad.header = ad.header.replace(" в СПб", "");
            }
            ad.text = "Ремонт " + vendor + " любой сложности срочно м.Сенная! Доступные цены, гарантия!";
            if (ad.text.length() > 75) {
                ad.text = ad.text.replace(" срочно", "");
            }
            checkLength(ad);

            ad.url = getRemontURL(vendor, urlPart);

            String stopPhrases = generateStopPhrases(processedModels);

            if (!vendor.equalsIgnoreCase("apple")) {
                generateRepairPhrases(sheet, ad, vendor + stopPhrases);
            }

            if (!(vendor.equalsIgnoreCase("htc") || vendor.equalsIgnoreCase("lg") || vendor.equalsIgnoreCase("apple"))) {
                generateRepairPhrases(sheet, ad, translit(vendor) + stopPhrases.toString());
            }
        }

        public static String generateStopPhrases(List<String> processedModels) {
            StringBuilder stopPhrases = new StringBuilder();
            for (String model : processedModels) {
                stopPhrases.append(" -").append(model.replaceAll("-", "").replaceAll("\\s", "").replaceAll("\\,", " -"));
            }
            return stopPhrases.toString();
        }

        private static void generateRepairPhrases(WritableSheet sheet, DirectAd ad, String phrase) throws WriteException {
            ad.phraseId = phraseCount++;
            ad.phrase = "ремонт " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "замена " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "не работает " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "прошить " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "перепрошить " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "разлочить " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "разлочка " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "разблокировать " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "разблокировка " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "русифицировать " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());

            ad.phraseId = phraseCount++;
            ad.phrase = "русификация " + phrase + REMONT_STOP_PHRASES;
            ExcelHelper.addAd(ad, sheet, getCount());
        }

    */
    public static boolean excludeModel(String model) {
        return model.equalsIgnoreCase("22") ||
                model.equalsIgnoreCase("30") ||
                model.equalsIgnoreCase("32") ||
                model.equalsIgnoreCase("100") ||
                model.equalsIgnoreCase("defy+") ||
                model.equalsIgnoreCase("phoenix") ||
                model.equalsIgnoreCase("101");
    }

    public static String getRemontURL(String vendor, String urlPart) {
        if (vendor.equalsIgnoreCase("htc") || vendor.equalsIgnoreCase("blackberry") || vendor.equalsIgnoreCase("siemens")) {
            return "http://extramobi.ru/auxpage_remont-telefonov-" + urlPart;
        } else if (vendor.equalsIgnoreCase("iphone")) {
            return "http://extramobi.ru/auxpage_remont-telefonov-" + urlPart;
        }
        return "http://extramobi.ru/auxpage_remont-sotovyh-telefonov-" + urlPart;
    }

    public static String translit(String vendor) {
        if (vendor.equalsIgnoreCase("nokia")) {
            return "нокиа";
        }
        if (vendor.equalsIgnoreCase("samsung")) {
            return "самсунг";
        }
        if (vendor.equalsIgnoreCase("sony ericsson")) {
            return "сони";
        }
        if (vendor.contains("iPhone")) {
            return vendor.replace("iPhone", "айфон");
        }
        if (vendor.contains("iPad")) {
            return vendor.replace("iPad", "айпад");
        }
        if (vendor.equalsIgnoreCase("siemens")) {
            return "сименс";
        }
        if (vendor.equalsIgnoreCase("fly")) {
            return "флай";
        }
        if (vendor.equalsIgnoreCase("alcatel")) {
            return "алкатель";
        }
        if (vendor.equalsIgnoreCase("motorola")) {
            return "моторола";
        }
        if (vendor.equalsIgnoreCase("philips")) {
            return "филипс";
        }
        if (vendor.equalsIgnoreCase("blackberry")) {
            return "блэкберри";
        }
//        throw new IllegalArgumentException("Unknown vendor: " + vendor);
        return null;
    }
}